#!/bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive
${HIVE_HOME} -S -e
SELECT
        dt,
        itcast_school_id  ,
        itcast_school_name,
        origin_type,
        itcast_subject_id ,
        itcast_subject_name,
       CASE
           WHEN GROUPING(dt,itcast_school_id,itcast_subject_id,origin_type) = 3
           --0011
               THEN 'school'
                 WHEN GROUPING(dt,itcast_school_id,itcast_subject_id,origin_type) = 2
                 --0010
               THEN 'origin_type_school'
                    WHEN GROUPING(dt,itcast_school_id,itcast_subject_id,origin_type) = 0
                    --0000
                    then 'subject_school'
                        WHEN GROUPING(dt,itcast_school_id,itcast_subject_id,origin_type) = 6
                        --0110
                        then 'origin_type'
           WHEN GROUPING(origin_type) = 0
               THEN 'origin_type'
           WHEN GROUPING(itcast_subject_name) = 0
               THEN 'subject_name'
           ELSE 'other' END AS group_type

from edu_dws.dws_signup_customer_relationship
GROUP BY
GROUPING SETS (
    (dt ,itcast_school_id, itcast_school_name),--每天+校区
    (dt ,origin_type, itcast_school_id, itcast_school_name),--每天+线上线下+校区
    (dt, origin_type, itcast_school_id, itcast_school_name, itcast_subject_id, itcast_subject_name),--每天+线上线下+学科
    (dt ,origin_type)
);
--天--校区
select
count(fact_customer_id) ,
dt,
itcast_school_id,
itcast_school_name
from
edu_dws.dws_signup_customer_relationship
where payment_state is not null
GROUP by dt,
itcast_school_id,
itcast_school_name;
--天+线上线下--校区
select
count(fact_customer_id) ,
dt,
origin_type ,
itcast_school_id,
itcast_school_name
from
edu_dws.dws_signup_customer_relationship
where payment_state is not null
GROUP by
dt,
origin_type ,
itcast_school_id,
itcast_school_name
;

--天+线上线下+校区+学科
select
count(fact_customer_id) ,
dt,
origin_type ,
itcast_school_id,
itcast_school_name,
itcast_subject_id ,
itcast_subject_name
from
edu_dws.dws_signup_customer_relationship
where payment_state is not null
GROUP by
dt,
origin_type ,
itcast_school_id,
itcast_school_name,
itcast_subject_id ,
itcast_subject_name
;





INSERT INTO hive.edu_dta.relationship_day_count
select

    origin_type ,
    itcast_school_id,
    itcast_school_name,
    itcast_subject_id ,
    itcast_subject_name,
CASE
           WHEN GROUPING(create_year,create_month,dt,itcast_school_id,itcast_subject_id,origin_type) = 3
           --000011
               THEN 'school'
                 WHEN GROUPING(create_year,create_month,dt,itcast_school_id,itcast_subject_id,origin_type) = 2
                 --0010
               THEN 'orgin_school'
                    WHEN GROUPING(create_year,create_month,dt,itcast_school_id,itcast_subject_id,origin_type) = 4
                    --0100
                    then 'subject'
                        WHEN GROUPING(create_year,create_month,dt,itcast_school_id,itcast_subject_id,origin_type) = 0
                        --0000
                            then 'orgin_subject'


           WHEN GROUPING(create_year,create_month,dt,itcast_school_id,itcast_subject_id,origin_type) = 19
           --0010011
               THEN 'month_school'
                 WHEN GROUPING(create_year,create_month,dt,itcast_school_id,itcast_subject_id,origin_type) = 10
                 --001010
               THEN 'month_orgin_school'
                    WHEN GROUPING(create_year,create_month,dt,itcast_school_id,itcast_subject_id,origin_type) = 12
                    --001100
                    then 'month_subject'
                        WHEN GROUPING(create_year,create_month,dt,itcast_school_id,itcast_subject_id,origin_type) = 8
                        --001000
                            then 'month_orgin_subject'

         WHEN GROUPING(create_year,create_month,dt,itcast_school_id,itcast_subject_id,origin_type) = 27
           --011011
               THEN 'year_school'
                 WHEN GROUPING(create_year,create_month,dt,itcast_school_id,itcast_subject_id,origin_type) = 26
                 --011010
               THEN 'year_orgin_school'
                    WHEN GROUPING(create_year,create_month,dt,itcast_school_id,itcast_subject_id,origin_type) = 28
                    --011100
                    then 'year_subject'
                        WHEN GROUPING(create_year,create_month,dt,itcast_school_id,itcast_subject_id,origin_type) = 24
                        --011000
                            then 'year_orgin_subject'

           ELSE 'other' END AS group_type,
           count(DISTINCT fact_customer_id )as count
FROM hive.edu_dws.dws_signup_customer_relationship
where payment_state is not null
GROUP BY
GROUPING SETS (
----天---
(create_year,create_month,dt),--天
(create_year,create_month,dt,itcast_school_id,itcast_school_name),--天+学校
(create_year,create_month,dt,origin_type ,itcast_school_id,itcast_school_name),--天+线上线下+学科
(create_year,create_month,dt,origin_type ,itcast_subject_id ,itcast_subject_name),--天+线上线下+学科
(create_year,create_month,dt,origin_type ,itcast_school_id,itcast_school_name,itcast_subject_id ,itcast_subject_name),--天+线上线下+学科+学校

---月-----
(create_year,create_month),
(create_year,create_month,itcast_school_id,itcast_school_name),
(create_year,create_month,origin_type ,itcast_school_id,itcast_school_name),
(create_year,create_month,origin_type ,itcast_subject_id ,itcast_subject_name),
(create_year,create_month,origin_type ,itcast_school_id,itcast_school_name,itcast_subject_id ,itcast_subject_name),

---年-----
(create_year),
(create_year,itcast_school_id,itcast_school_name),
(create_year,origin_type ,itcast_school_id,itcast_school_name),
(create_year,origin_type ,itcast_subject_id ,itcast_subject_name),
(create_year,origin_type ,itcast_school_id,itcast_school_name,itcast_subject_id ,itcast_subject_name));






--===============================================================================================
INSERT into edu_dta.origin_channel_count
SELECT
    origin_type ,
    origin_channel ,
    tdepart_name,
CASE--天
    when GROUPING(origin_type ,create_year,create_month,dt,origin_channel,tdepart_name ) = 1
    --000001
    then 'coun_origin_channel '
    when GROUPING(origin_type ,create_year,create_month,dt,origin_channel,tdepart_name ) = 2
    --000010
    then 'coun_name '

    --月
    when GROUPING(origin_type ,create_year,create_month,dt,origin_channel,tdepart_name ) = 5
    --000101
    then 'coun_origin_channel '
    when GROUPING(origin_type ,create_year,create_month,dt,origin_channel,tdepart_name ) = 6
    --000110
    then 'coun_name '

    --年
    when GROUPING(origin_type ,create_year,create_month,dt,origin_channel,tdepart_name ) = 13
    --001101
    then 'coun_origin_channel '
    when GROUPING(origin_type ,create_year,create_month,dt,origin_channel,tdepart_name ) = 14
    --001110
    then 'coun_name '
    ELSE 'other' END AS group_type,
    count(DISTINCT fact_customer_id )as count
FROM hive.edu_dws.dws_signup_customer_relationship
where
origin_type is not null
GROUP BY
GROUPING SETS (
                (create_year,create_month,dt),--天
                (origin_type ,create_year,create_month,dt,origin_channel),
                (origin_type ,create_year,create_month,dt,tdepart_name ),

                (origin_type ,create_year,create_month),--天
                (origin_type ,create_year,create_month,origin_channel),
                (origin_type ,create_year,create_month,tdepart_name ),

                (create_year),--天
                (origin_type ,create_year,origin_channel),
                (origin_type ,create_year,tdepart_name )          );

--
INSERT into edu_dta.relationship_count
SELECT
  origin_type,
 CASE--天
    when GROUPING(origin_type,create_year,create_month,dt)=0
    --0000
    THEN 'day_relationship'
     when GROUPING(create_year,create_month,dt)=1
     --0001
      THEN 'month_relationship'
    when GROUPING(create_year,create_month,dt)=3
     --0011
      THEN 'year_relationship'
ELSE 'other' END AS group_type,
    (COUNT(payment_state)/COUNT(fact_customer_id ))as count_,
     (COUNT(payment_state)/COUNT(clue_state))as origin_
FROM  hive.edu_dws.dws_signup_customer
where
origin_type is not null
GROUP BY
GROUPING SETS (
(origin_type,create_year,create_month,dt),
(origin_type,create_year,create_month),
(origin_type,create_year)
);


CREATE TABLE mysql.yipin.test AS
SELECT
*
FROM hive.edu_dta.origin_channel_count;

CREATE TABLE mysql.yipin.test1 AS
SELECT
*
FROM hive.edu_dta.relationship_count;

CREATE TABLE mysql.yipin.test2 AS
SELECT
*
FROM hive.edu_dta.relationship_day_count;

CREATE TABLE mysql.yipin.t1 AS
SELECT
*
FROM hive.edu_dws.dws_signup_customer;
CREATE TABLE mysql.yipin.t2 AS
SELECT
*
FROM hive.edu_dws.dws_signup_customer;